﻿using KalevaAalto.DataPart.Base;
using KalevaAalto.DataPart.DataBasePart;
using KalevaAalto.DataPart.DataBasePart.Attributes;
using KalevaAalto.DataPart.Excel.Base;
using KalevaAalto.DataPart.Excel.Epplus;
using KalevaAalto.DataPart.Models;
using KalevaAalto.TypePart;
using MySqlX.XDevAPI.Common;
using NetTaste;
using Org.BouncyCastle.X509.Store;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Reflection.Metadata;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace KalevaAalto
{
    public static partial class Static
    {
        public static BaseWorkbook GetWorkbook() => GetEpplusWorkbook();
        public static BaseWorkbook GetWorkbook(string fileName) => GetEpplusWorkbook(fileName);

        public static BaseWorkbook GetEpplusWorkbook() => new EpplusWorkbook();
        public static BaseWorkbook GetEpplusWorkbook(string fileName) => new EpplusWorkbook(fileName);

        public static DataBaseBase GetSqlLite() => new SqlLite();
        public static DataBaseBase GetSqlLite(string fileName) => new SqlLite() { FileName = fileName };

        public static async Task<TResult[]> ToArrayAsync<TResult>(this ISugarQueryable<TResult> queryable,CancellationToken token)
            =>(await queryable.ToListAsync(token)).ToArray();

        public static string GetCompareColumnsString(KeyValuePair<string,string> pair,IEnumerable<string> keyColumnNames, IEnumerable<string> valueColumnNames)
        {
            ISet<string> keyColumnNameCollection = keyColumnNames.ToHashSet();
            ISet<string> valueColumnNameCollection = valueColumnNames.ToHashSet();
            if (keyColumnNameCollection.SetEquals(valueColumnNameCollection))
            {
                return string.Empty;
            }
            else
            {
                StringBuilder result = new StringBuilder();

                result.Append($"{pair.Key}中共有{keyColumnNameCollection.Count}个字段；");
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (string keyColumnName in keyColumnNameCollection)
                    {
                        if (!valueColumnNameCollection.Contains(keyColumnName)) sb.Append($"【{keyColumnName}】");
                    }
                    if (sb.Length > 0)
                    {
                        result[result.Length - 1] = '，';
                        result.Append($"比{pair.Value}多出来的字段有{sb}；");
                    }
                }
                result.Append($"{pair.Value}中共有{valueColumnNameCollection.Count}个字段；");
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (string valueColumnName in valueColumnNameCollection)
                    {
                        if (!keyColumnNameCollection.Contains(valueColumnName)) sb.Append($"【{valueColumnName}】");
                    }
                    if (sb.Length > 0)
                    {
                        result[result.Length - 1] = '，';
                        result.Append($"比{pair.Key}多出来的字段有{sb}；");
                    }
                }

                return result.ToString();
            }
        }


        public static string GetCompareColumnsString(string keyTableName,string valueTableName, IEnumerable<string> keyColumnNames, IEnumerable<string> valueColumnNames)
        {
            return GetCompareColumnsString(new KeyValuePair<string, string>(keyTableName, valueTableName), keyColumnNames, valueColumnNames);
        }

        public static void ForEach<T>(this IEnumerable<T>? objs,Action<T>? action)
        {
            if(action is null || objs is null)
            {
                return;
            }

            foreach(T obj in objs)
            {
                action(obj);
            }

        }



        



        #region Get Entity Information
        public static string GetTableName(Type type,string tableName = EmptyString)
        {
            SugarTable? table = type.GetCustomAttribute<SugarTable>();
            if (!string.IsNullOrEmpty(tableName))
            {
                return tableName;
            }
            else if (table is not null && !string.IsNullOrEmpty(table.TableName))
            {
                return table.TableName;
            }
            else
            {
                return type.Name;
            }
        }


        public static string GetTableName<T>(string tableName = EmptyString) => GetTableName(typeof(T),tableName);
        public static IDictionary<string, PropertyInfo> GetColumnInfoDic(Type objType,bool includingIgnore = true, bool includingReadOnly = true)
        {
            IDictionary<string, PropertyInfo> columnInfos = new Dictionary<string, PropertyInfo>();
            IEnumerable<PropertyInfo> propertyInfos = objType.GetProperties();

            foreach (var property in propertyInfos)
            {
                SugarColumn? sugarColumn = property.GetCustomAttribute<SugarColumn>();
                if (sugarColumn is not null 
                    && !string.IsNullOrEmpty(sugarColumn.ColumnName) 
                    && ( includingIgnore || (!sugarColumn.IsIgnore) )
                    && (includingReadOnly || (!sugarColumn.IsOnlyIgnoreInsert))
                    )
                {
                    columnInfos.Add(sugarColumn.ColumnName,property);
                }
            }

            columnInfos = columnInfos.ToImmutableDictionary();

            return columnInfos;
        }

        public static IDictionary<string, PropertyInfo> GetColumnInfoDic<T>(bool includingIgnore = true,bool includingReadOnly = true)
        {
            return GetColumnInfoDic(typeof(T), includingIgnore, includingReadOnly);
        }




        public static IDictionary<string, PropertyInfo> GetColumnInfoDic(Type objType, IEnumerable<string>? selectColumnNames)
        {

            if (selectColumnNames is null)
            {
                return GetColumnInfoDic(objType);
            }

            IDictionary<string, ushort> selectColumnNameIndex = selectColumnNames
                .ToList()
                .Select((it, index) => new { Value = it, Index = index })
                .ToImmutableDictionary(it=>it.Value,it=>(ushort)it.Index);
            if (selectColumnNameIndex.Count<=0)
            {
                return GetColumnInfoDic(objType);
            }
            IDictionary<string, PropertyInfo?> columnInfos = selectColumnNameIndex
                .Select(it=>it.Key)
                .Select(it => new KeyValuePair<string, PropertyInfo?>(it, null))
                .ToDictionary(it=>it.Key,it=>it.Value);

            foreach (var property in objType.GetProperties())
            {
                SugarColumn? sugarColumn = property.GetCustomAttribute<SugarColumn>();
                if (sugarColumn is not null && !string.IsNullOrEmpty(sugarColumn.ColumnName) && columnInfos.ContainsKey(sugarColumn.ColumnName))
                {
                    columnInfos[sugarColumn.ColumnName] = property;
                }
                else if (columnInfos.ContainsKey(property.Name))
                {
                    columnInfos[property.Name] = property;
                }
            }

            return columnInfos
                .Where(it => it.Value is not null)
                .OrderBy(it => selectColumnNameIndex[it.Key])
                .ToDictionary(it=>it.Key,it=>it.Value)!;
        }


        #endregion

        #region Array And DataTable
        public static T[] GetArray<T>(this DataTable dataTable) where T : new()
        {
            // 获取对象的类型
            ICollection<T> objs = new List<T>();
            Type objType = typeof(T);
            IDictionary<string, PropertyInfo> columnInfoDic = GetColumnInfoDic(objType, dataTable.Columns.Cast<DataColumn>().Select(it => it.ColumnName));

            foreach (DataRow dataRow in dataTable.Rows)
            {
                T obj = new T();
                foreach (var item in columnInfoDic)
                {
                    object? value = item.Value.PropertyType.GetValue(dataRow[item.Key]);
                    item.Value!.SetValue(obj, value);
                }
                objs.Add(obj);
            }

            return objs.ToArray();
        }

        public static DataTable GetDataTable<T>(this IEnumerable<T> objs,string tableName = EmptyString ,bool includingIgnore = true)
        {
            tableName = GetTableName<T>(tableName);
            DataTable dataTable = new DataTable(tableName);
            IDictionary<string, PropertyInfo> columnInfos = GetColumnInfoDic(typeof(T), includingIgnore);

            foreach(var item in columnInfos)
            {
                Type? underlyingType = Nullable.GetUnderlyingType(item.Value.PropertyType);
                if(underlyingType is not null)
                {
                    dataTable.Columns.Add(item.Key, underlyingType);
                }
                else
                {
                    dataTable.Columns.Add(item.Key, item.Value.PropertyType);
                }
            }
            
            if (dataTable.Columns.Count <= 0)throw new Exception($"表格“{tableName}”没有字段；");
            


            foreach (T obj in objs)
            {
                DataRow dataRow = dataTable.Rows.Add();
                foreach(var item in columnInfos)
                {
                    object? valueSource = item.Value.GetValue(obj);
                    object? value = item.Value.PropertyType.GetValue(valueSource);
                    if (value is not null) dataRow[item.Key] = value;
                }
            }
            return dataTable;
        }


        


        public static DataTable GetDataTable<T>(this IEnumerable<T> objs,string tableName, IEnumerable<string> selectColumnNames)
        {
            selectColumnNames = selectColumnNames.ToArray();
            tableName = GetTableName<T>(tableName);

            if (selectColumnNames.Count() <= 0) throw new Exception($"表格“{tableName}”没有字段；");
            DataTable dataTable = new DataTable(tableName);

            IDictionary<string, PropertyInfo> columnInfos = GetColumnInfoDic(typeof(T),selectColumnNames);
            foreach (var item in columnInfos) dataTable.Columns.Add(item.Key, item.Value.PropertyType);
            if (dataTable.Columns.Count <= 0) throw new Exception($"表格“{tableName}”没有字段；");



            foreach (T obj in objs)
            {
                DataRow dataRow = dataTable.Rows.Add();
                foreach (var item in columnInfos)
                {
                    object? value = item.Value.GetValue(obj);
                    if (value is not null) dataRow[item.Key] = value;
                }
            }
            return dataTable;
        }

        





        private readonly static Regex s_newColumnNameRegex = new Regex(@"(?<name>[^\d^\s].*)(?<number>\d*)", RegexOptions.Compiled);
        public static string GetNewColumnName(string? columnName,IEnumerable<string> columnNameCollection)
        {
            columnNameCollection = columnNameCollection.ToImmutableHashSet();
            if (string.IsNullOrEmpty(columnName) || !s_newColumnNameRegex.IsMatch(columnName))return string.Empty;
            int count = 0;
            while (columnNameCollection.Contains(columnName))
            {
                Match match = s_newColumnNameRegex.Match(columnName);
                columnName = match.Groups[@"name"].Value + count.ToString();
            }
            return columnName;
        }
        #endregion



        /// <summary>
        /// 获取DataTable对象的csv格式字符串
        /// </summary>
        /// <param name="dataTable">要生成csv格式字符串</param>
        /// <param name="maxCount">要限制生成的csv格式字符串的数据行数</param>
        /// <returns>返回dataTable的csv格式字符串</returns>
        public static string CSV(this DataTable dataTable, int maxCount = int.MaxValue)
        {
            //检查DataTable是否为空表，是的话就返回空字符串
            if (dataTable.Columns.Count <= 0)
            {
                return string.Empty;
            }


            //创建要生成csv字符串的可变字符串对象
            StringBuilder result = new StringBuilder();

            //录入字段
            foreach (DataColumn column in dataTable.Columns)
            {
                result.Append(column.ColumnName);
                result.Append(',');
            }
            result.Remove(result.Length - 1, 1);
            result.Append('\n');


            //录入数据
            int counter = 0; //定义计数器
            foreach (DataRow dataRow in dataTable.Rows)
            {
                if (counter > maxCount)
                {
                    break;//如果计数器大于所限制的行数，则退出
                }
                foreach (object? cell in dataRow.ItemArray)
                {
                    result.Append(cell);
                    result.Append(',');
                }
                result.Remove(result.Length - 1, 1);
                result.Append('\n');
                counter++;
            }
            result.Remove(result.Length - 1, 1);



            return result.ToString();
        }

        /// <summary>
        /// 此函数不规范，建议不要使用
        /// </summary>
        public static void Insert(this DataTable to_table, DataTable src_table)
        {
            HashSet<string> to_table_column_names = new HashSet<string>();
            foreach (DataColumn to_table_column in to_table.Columns)
            {
                to_table_column_names.Add(to_table_column.ColumnName);
            }


            foreach (DataRow src_table_row in src_table.Rows)
            {
                DataRow to_table_row = to_table.Rows.Add();
                foreach (DataColumn src_table_column in src_table.Columns)
                {
                    if (to_table_column_names.Contains(src_table_column.ColumnName))
                    {
                        to_table_row[src_table_column.ColumnName] = src_table_row[src_table_column.ColumnName];
                    }
                }
            }


        }


        /// <summary>
        /// 检查DataTable对象是否含有名为fieldName的字段，是的话就返回true，否则返回false
        /// </summary>
        /// <param name="dataTable">要检查的DataTable对象</param>
        /// <param name="columnName">要检查的字段名称</param>
        public static bool IsExistsColumn(this DataTable dataTable, string columnName)
        {
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                if (dataColumn.ColumnName == columnName) return true;
            }
            return false;
        }


        /// <summary>
        /// 检查DataTable对象是否含有名为fieldNames中字符名称的所有字段，是的话就返回true，否则返回false
        /// </summary>
        /// <param name="dataTable">要检查的DataTable对象</param>
        /// <param name="columnNames">要检查的字段名称数组</param>
        public static bool IsExistsColumn(this DataTable dataTable, string[] columnNames)
        {
            #region 初步检查
            if (dataTable.Columns.Count < columnNames.Length)
            {
                return false;
            }
            #endregion

            #region 创建dataTable字段名称的哈希表，并录入
            HashSet<string> dataTableColumnNamesHashSet = new HashSet<string>();
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                dataTableColumnNamesHashSet.Add(dataColumn.ColumnName);
            }
            #endregion

            //逐个检查字段名称是否存在
            foreach (string fieldName in columnNames)
            {
                if (!dataTableColumnNamesHashSet.Contains(fieldName)) return false;
            }


            return true;
        }


        /// <summary>
        /// 以某种描述，筛选DataTable中的行
        /// </summary>
        /// <param name="dataTable">要筛选的DataTable对象</param>
        /// <param name="conditions">筛选描述字符串</param>
        /// <returns>返回DataTable对象筛选后的DataTable对象</returns>
        public static DataTable TableSelect(this DataTable dataTable, string conditions)
        {
            DataRow[] dataRows = dataTable.Select(conditions);
            dataTable = dataTable.Clone();
            foreach (DataRow row in dataRows) dataTable.Rows.Add(row.ItemArray);
            return dataTable;
        }



        /// <summary>
        /// 此函数不规范，建议不要使用
        /// </summary>
        public static DataTable TableSelect(this DataTable dataTable, string[] column_names, string? conditions = null)
        {
            if (!string.IsNullOrEmpty(conditions)) dataTable = dataTable.TableSelect(conditions);
            DataTable new_dt = new DataTable();

            //获取字段名称列表
            HashSet<string> strings = new HashSet<string>();
            foreach (DataColumn column in dataTable.Columns) strings.Add(column.ColumnName);

            foreach (string column_name in column_names)
            {
                if (strings.Contains(column_name)) new_dt.Columns.Add(column_name, dataTable.Columns[column_name]?.DataType ?? typeof(string));
            }

            //录入数据
            foreach (DataRow row in dataTable.Rows)
            {
                DataRow new_row = new_dt.NewRow();
                foreach (DataColumn column in new_dt.Columns)
                {
                    new_row[column.ColumnName] = row[column.ColumnName];
                }
                new_dt.Rows.Add(new_row);
            }

            return new_dt;
        }


        /// <summary>
        /// 此函数不规范，建议不要使用
        /// </summary>
        public static object? Sum(this DataTable dataTable, string column_name, string? conditions = null)
        {
            DataRowCollection dataRows;
            if (conditions == null) dataRows = dataTable.Rows;
            else dataRows = dataTable.TableSelect(conditions).Rows;



            Type type = dataTable.Columns[column_name]?.DataType ?? typeof(object);
            if (type == typeof(int))
            {
                int rs = 0;
                foreach (DataRow row in dataRows) rs += (int)row[column_name];
                return rs;
            }
            else if (type == typeof(short))
            {
                short rs = 0;
                foreach (DataRow row in dataRows) rs += (short)row[column_name];
                return rs;
            }
            else if (type == typeof(long))
            {
                long rs = 0;
                foreach (DataRow row in dataRows) rs += (long)row[column_name];
                return rs;
            }
            else if (type == typeof(uint))
            {
                uint rs = 0;
                foreach (DataRow row in dataRows) rs += (uint)row[column_name];
                return rs;
            }
            else if (type == typeof(ushort))
            {
                ushort rs = 0;
                foreach (DataRow row in dataRows) rs += (ushort)row[column_name];
                return rs;
            }
            else if (type == typeof(ulong))
            {
                ulong rs = 0;
                foreach (DataRow row in dataRows) rs += (ulong)row[column_name];
                return rs;
            }
            else if (type == typeof(double))
            {
                double rs = 0;
                foreach (DataRow row in dataRows) rs += (double)row[column_name];
                return rs;
            }
            else if (type == typeof(decimal))
            {
                decimal rs = 0;
                foreach (DataRow row in dataRows) rs += (decimal)row[column_name];
                return rs;
            }
            else if (type == typeof(string))
            {
                StringBuilder rs = new StringBuilder();

                foreach (DataRow row in dataRows)
                {
                    rs.Append((string)row[column_name]);
                    rs.Append(',');
                }
                if (rs.Length > 0) rs.Remove(rs.Length - 1, 1);

                return rs.ToString();
            }




            return null;
        }


        /// <summary>
        /// 以某种描述，对DataTable中的行进行排序
        /// </summary>
        /// <param name="dataTable">要排序的DataTable对象</param>
        /// <param name="conditions">排序描述字符串</param>
        /// <returns>返回DataTable对象排序后的DataTable对象</returns>
        public static DataTable Sort(this DataTable dataTable, string conditions)
        {
            dataTable.DefaultView.Sort = conditions;
            return dataTable.DefaultView.ToTable();
        }

        /// <summary>
        /// 以某种描述，对DataTable中的行进行排序
        /// </summary>
        /// <param name="dataTable">要排序的DataTable对象</param>
        /// <param name="comparison">排序描述</param>
        /// <returns>返回DataTable对象排序后的DataTable对象</returns>
        public static DataTable Sort(this DataTable dataTable, Comparison<DataRow> comparison)
        {
            DataTable newDt = dataTable.Clone();
            List<DataRow> dataRows = new List<DataRow>();
            foreach (DataRow row in dataTable.Rows) dataRows.Add(row);
            dataRows.Sort(comparison);
            foreach (DataRow row in dataRows) newDt.Rows.Add(row.ItemArray);
            return newDt;
        }

        /// <summary>
        /// 以某个字段名，获取其在某个DataTable对象中的字段序数
        /// </summary>
        /// <param name="dataTable">要查找的DataTable对象</param>
        /// <param name="columnName">要查找的字段名称</param>
        /// <returns>返回dataTable中名为columnName字段所在的字段序数，找不到名为columnName字段就返回_1</returns>
        public static int ColumnNumber(this DataTable dataTable, string columnName)
        {
            int count = dataTable.Columns.Count;
            for (int i = 0; i < count; i++)
            {
                if (dataTable.Columns[i].ColumnName == columnName) return i;
            }
            return -1;
        }

        /// <summary>
        /// 找到数据表中某个字段的序号
        /// </summary>
        /// <param name="dataTable">数据表</param>
        /// <param name="dataColumnName">字段名称</param>
        /// <returns>返回dataTable中名为dataColumnName字段的序号，找不到就返回_1</returns>
        public static int FindColumn(this DataTable dataTable, string dataColumnName)
        {
            int dataColumnCount = dataTable.Columns.Count;

            for (int i = 0; i < dataColumnCount; i++)
            {
                if (dataTable.Columns[i].ColumnName == dataColumnName)
                {
                    return i;
                }
            }
            return -1;
        }




    }
}
